/* ********************************************************************************* */
/* 8 *************************         IPO                ************************** */
/* ********************************************************************************* */
/* Summary   : Code builds dataset for IPO test. IPO data from Ritter's website      */
/* ********************************************************************************* */


	PROC import out=ipo FILE='C:\data\IPO.xlsx'  dbms=xlsx replace ; run;	

	data ipo; set ipo; if 2018>=year(offerdate)>=1990; iporow=_n_; run;
	proc sql; create table ipocow as select * from ipo as a left join cow.cow as b 
	on substr(a.cusip,1,6)=substr(b.cusip,1,6) ; quit;
	proc sort data=ipocow; by iporow fyenddt; run;
	data ipocow; set ipocow; by iporow; if first.iporow; if incorp~="";run;

	* make a 51 states file, use SAS cow datafile created in "7 event study car roa.sas";
	data state; set cow.cow;
	if fyear=1997; if incorp in ("AS", "INTL", "PR", "TT", "VI", "") then delete;
	keep  incorp; proc sort nodupkey; by  incorp; run; 

	* make an industry file;
	data ind; set cow.cow; 
	if year(fyenddt)>=1990; if incorp~="";  
	ind=indname12; 
	keep ind; if ind~="";
	proc sort nodupkey; by ind; run; 
	
	* count number of firms in each state and industry at year end; 
	data firm_state_ind_yr; set cow.cow; 
	ind=indname12;
	if incorp~=""; if lpermno~=.; if mb<0 then mb=0; if mb>10 then mb=10;
	yr=year(fyenddt); permno=lpermno; mktcap_mb=mktcap*mb  ;mktcap_stockreturn=mktcap*stockreturn; 
	if yr>=1990;
	keep ind yr stockreturn mktcap_stockreturn mktcap mb mktcap_mb permno incorp; run;
	proc sort; by incorp ind yr; run;
	proc means noprint; by incorp ind yr; var mktcap_stockreturn mktcap_mb mktcap ; 
	output out=state_ind_yr sum=mktcap_stockreturn_sum mktcap_mb_sum mktcap_sum; run;
	data state_ind_yr; set state_ind_yr; rename _freq_=nfirms; drop _type_; 
	vwret=mktcap_stockreturn_sum/mktcap_sum;
	vwmb=mktcap_mb_sum/ mktcap_sum;run;
	
	* mkt stock returns by year, using monthly stock return file from CRSP;
	data mktret; set data.msi; yr=year(date); l_vwretd=log(1+vwretd); mktret=vwretd;
	keep yr mktret l_vwretd; if yr>=1990;	run;
	proc sort data=mktret; by yr; run;
	proc means noprint data=mktret; by yr ; 
	output out = mktret_yr (drop= _:) sum = rm; var l_vwretd; run;
	data mktret_yr; set mktret_yr; mktret = exp(rm)-1; drop rm; run;
		
	* state x ind x year;
	proc sql; create table stateind as select * from state, ind; quit;
	proc sql; create table stateindyr as select * from stateind, mktret_yr; quit;

	proc sql; create table panel as select * from stateindyr as a left join state_ind_yr as b 
	on a.incorp=b.incorp and a.ind=b.ind and a.yr=b.yr; quit;
	proc sort data=panel;by incorp ind yr; run;

	data ipotemp; set ipocow; if vcdummy in (1,2) then vc=1; else vc=0; count=1; vcmktcap=vc*mktcap;
	keep count offerdate incorp vc mktcap vcmktcap; run;
	proc sql; create table panel_ipo as select * from panel as a left join ipotemp as b 
	on a.yr=year(b.offerdate) and a.incorp=b.incorp; quit;
	proc sort data=panel_ipo; by incorp ind yr; run; 

	* get number of IPOs per state x ind x year;
	proc means noprint data=panel_ipo; by  incorp ind yr nfirms vwmb vwret mktret; var vc vcmktcap count mktcap;
	output out=ipo_state_ind_yr (drop=_:) sum=vcnumber vcvolume iponumber ipovolume;	run; 

	* fill missing with 0;
	proc stdize data=ipo_state_ind_yr reponly missing=0 out=ipo_state_ind_yr_0; 
	var vcnumber vcvolume iponumber ipovolume;
	run;

	* get state vars including employment, GDP, population by state from US Census Bureau;
	proc import datafile="C:\data\statesvars.xls" out=employment dbms=xls replace; sheet="employment"; run;
	proc import datafile="C:\data\statesvars.xls" out=gdp dbms=xls replace; sheet="gdp"; run;
	proc import datafile="C:\data\statesvars.xls" out=population dbms=xls replace; sheet="population"; run;

	%macro states;
	%do i = 1969 %to 2019;
	data e_year_&i; set employment; year=&i; stateemployment=year_&i; keep state year stateemployment; proc sort; by state; run;
	data g_year_&i; set gdp; year=&i; stategdp=year_&i; keep state year stategdp; proc sort; by state; run;
	data p_year_&i; set population; year=&i; statepopulation=year_&i; keep state year statepopulation; proc sort; by state; run;
	data year_&i; merge e_year_&i g_year_&i p_year_&i; by state; run; proc sql; drop table e_year_&i, g_year_&i, p_year_&i; quit;
	%end;
	data statesvars; set %do i = 1969 %to 2019; year_&i %end;; run;
	proc sql; %do i = 1969 %to 2019; drop table year_&i; %end; quit; run;
	%mend states;
	%states;

	data statesvars; set statesvars; l_state=lag(state); l_stateemployment=lag(stateemployment); 
	l_stategdp=lag(stategdp); l_statepopulation=lag(statepopulation); run;
	data statesvars; set statesvars; if l_state=state then do; 
	pctchg_stateemployment=stateemployment/l_stateemployment-1;
	pctchg_stategdp=stategdp/l_stategdp-1;
	pctchg_statepopulation=statepopulation/l_statepopulation-1;
	end; run;

	* Import CPI and longterm bond returns from FRED ST LOUIS;
	PROC IMPORT out= INTGSBUSM193N FILE= "C:\data\INTGSBUSM193N.csv" DBMS=csv REPLACE;RUN;

	data INTGSBUSM193N; set INTGSBUSM193N; if month(date)=12; run;
   	proc sql; create table ipo_bond as select * from ipo_state_ind_yr_0 as a left join INTGSBUSM193N as b 
	on  a.yr=year(b.date) ; quit;

	proc sql; create table ipopanel as select * from ipo_bond as a left join statesvars as b 
	on  a.incorp=b.state and a.yr=b.year ; quit;

	proc sort; by   incorp ind yr; run;
	data ipopanel; set ipopanel; if 2018>=yr>=1990; rename INTGSBUSM193N=ltbond;
	if mdy(3,1,2001)<=date<=mdy(11,30,2001) or mdy(12,1,2007)<=date<=mdy(6,30,2009) then nber=0; else nber=1;
	run;
	data ipopanel; set ipopanel;
	if iponumber=. then iponumber=0; n_ipo=iponumber/nfirms; 
	if ipovolume=. then ipovolume=0; v_ipo=ipovolume/1000	/nfirms;
	if vcnumber=. then vcnumber=0; n_vc=vcnumber/nfirms; 
	if vcvolume=. then vcvolume=0; v_vc=vcvolume/1000/nfirms;

	vc_prop=vcnumber/iponumber; if vc_prop=. then vc_prop=0;	
	ln_iponumber=log(1+iponumber); l_ln_iponumber=lag(ln_iponumber);
	ln_ipovolume=log(1+ipovolume); l_ln_ipovolume=lag(ln_ipovolume);
	ln_vcnumber=log(1+vcnumber); l_ln_vcnumber=lag(ln_vcnumber);
	ln_vcvolume=log(1+vcvolume); l_ln_vcvolume=lag(ln_vcvolume);
	ln_n_ipo=log(1+n_ipo); l_ln_n_ipo=lag(ln_n_ipo);
	ln_v_ipo=log(1+v_ipo); l_ln_v_ipo=lag(ln_v_ipo);
	ln_n_Vc=log(1+n_vc); l_ln_n_vc=lag(ln_n_vc);
	ln_v_vc=log(1+v_vc); l_ln_v_vc=lag(ln_v_vc);
	ln_vc_prop=log(1+vc_prop); l_ln_vc_prop=lag(ln_vc_prop);
	run;
		
	proc sort data=ipopanel; by incorp ind yr; run;

	data ipopanel; set ipopanel; l_mktret=lag(mktret); l_vwmb=lag(vwmb); l_vwret=lag(vwret); l_ltbond=lag(ltbond); l_tcu=lag(tcu);run;

	PROC EXPORT DATA= ipopanel OUTFILE= "C:\cow\ipopanel.dta" DBMS=STATA REPLACE;RUN;


